package www.admin.utils;

import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;



public class ExportExcel<T> {
	public HSSFWorkbook exportExcel(String title, String[] headers,String[] Col,Collection<T> dataset, String pattern) {
		if(pattern == null || pattern.equals("")) pattern = "yyyy-MM-dd HH:mm:ss";
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(title);
		sheet.setDefaultColumnWidth(15);
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		HSSFRow row = sheet.createRow(0);
		int Cell = 0;
		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(Cell);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
			Cell ++ ;
		}
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			String[] fields = Col;
			Cell = 0;
			for (short i = 0; i < fields.length; i++) {
				String fieldName = fields[i];
				HSSFCell cell = row.createCell(Cell);
				try {
					Object value = "";
					Class tCls = null;
					Map map = null;
					if(t instanceof Map){
						map = (Map)t;
						value = map.get(fieldName);
					} else {
						String getMethodName = "get"
								+ fieldName.substring(0, 1).toUpperCase()
								+ fieldName.substring(1);
						tCls = t.getClass();
						Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
						value = getMethod.invoke(t, new Object[] {});
					}
					if(value == null ) value = "";
					String textValue = null;
					if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (value instanceof byte[]) {
						row.setHeightInPoints(60);
						sheet.setColumnWidth(Cell, (short) (35.7 * 80));
						byte[] bsValue = (byte[]) value;
						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
								1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(2);
						patriarch.createPicture(anchor, workbook.addPicture(
								bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
					} else {
						textValue = value.toString();
					}
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(
									textValue);
							HSSFFont font3 = workbook.createFont();
							font3.setColor(HSSFColor.BLUE.index);
//							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
					Cell ++ ;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return workbook;
	}
	

	public static Hashtable<String,String[]> getExcelCol(String jsonString) {
    	Hashtable<String,String[]> table = new Hashtable<String, String[]>();
		if(jsonString == null || "".equals(jsonString)){
			 return table; 
		}
		String[] sourceStrArray = jsonString.split(",");
		String[] headers = new String[sourceStrArray.length];
    	String[] fields = new String[sourceStrArray.length];
		for (int i = 0; i < sourceStrArray.length; i++) {
			String[] title = sourceStrArray[i].split(":");
			headers[i] = title[0];
			fields[i] = title[1];
		}
		table.put("headers", headers);
		table.put("fields", fields);
        return table; 
    }  
	

	public static String excel(HttpServletResponse response,List<Map<String, Object>> list, String titie, String header) {
		ExportExcel<Map<String, Object>> ex = new ExportExcel<Map<String, Object>>();
		Map<String, String[]> map = ExportExcel.getExcelCol(header);
		String[] headers = map.get("headers");
		String[] col = map.get("fields");
		String dateTime = null;
		try {
			HSSFWorkbook workbook = ex.exportExcel(titie, headers, col,list, null);
			String date = DateUtil.formatDate(DateUtil.getCurrentDate(),DateUtil.FORMAT_DATE_YYYY_MM_DD);
			response.setContentType("application/vnd.ms-excel");
			if (!StringUtil.isNullOrBlank(dateTime)) {
				titie=titie+".xls";
				response.setHeader("Content-disposition","attachment;filename="+ dateTime+ new String(titie.getBytes("gb2312"),"iso8859-1"));
			} else {
				titie=titie+".xls";
				response.setHeader("Content-disposition","attachment;filename="+ date+ new String(titie.getBytes("gb2312"),"iso8859-1"));
			}
			OutputStream ouputStream = response.getOutputStream();
			workbook.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return null;
	}

}
